#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 创建,删除scylla数据表【每周执行一次即可】
# 作者：王成
# 日期：2017-04-19
from optparse import OptionParser
import sys
import datetime,sys,os,timeit
from cassandra import ConsistencyLevel
from cassandra.cluster import Cluster
from cassandra.query import SimpleStatement,BatchStatement
from cassandra import OperationTimedOut,WriteTimeout,InvalidRequest
import traceback
import StringIO
import yaml
reload(sys)
sys.setdefaultencoding('utf-8')
def create_table():
    start = timeit.default_timer()
    try:
        config_file = open(os.path.dirname(os.path.abspath(__file__)) + '/config.yaml')
        config = yaml.safe_load(config_file)
        config_file.close()    
        cluster = Cluster(contact_points = config['scylla']['hosts'], port = 9042,max_schema_agreement_wait = 60)
        #cluster = Cluster(contact_points = ["172.22.0.75","172.22.0.76"], port = 9042,max_schema_agreement_wait = 30)
        print 'connect scylla...'
        session = cluster.connect()
        KEYSPACE = "yisa_oe"
        print 'create keyspace yisa_oe'
        session.execute("""
            CREATE KEYSPACE IF NOT EXISTS %s
            WITH replication = { 'class': 'SimpleStrategy', 'replication_factor': '2' }
            """ % KEYSPACE)
        session.set_keyspace(KEYSPACE)
        today = datetime.datetime.now()
        for d in xrange(-180,30):#240个没问题 测试540个
            day = today + datetime.timedelta(days = d)
            ds = day.strftime('%Y%m%d')
            dss = day.strftime('%Y%m%d%H')
            print 'create table',ds,
            start = timeit.default_timer()
            # if d>=0:
                # session.execute('''
                    # CREATE TABLE IF NOT EXISTS today_%s(s int,t_h int,t_m int,info_id uuid,capture_time timestamp,data_bin blob,region_id bigint,location_id bigint,location_type_id int,device_id bigint,lane_id int,direction_id int,speed int,license_plate text,lp_type int,image_url1 text,image_url2 text,image_url3 text,has_image int,last_captured int,PRIMARY KEY (s,t_h,t_m,info_id));
                    # ''' % ds,timeout=60)
            if d>=0 and d<=2:
                for h in xrange(0,24):
                    dayh = day + datetime.timedelta(hours = h)
                    dss = dayh.strftime('%Y%m%d%H')
                    print 'create table hours',dss,
                    session.execute('''
                        CREATE TABLE IF NOT EXISTS today_%s(info_id uuid,capture_time timestamp,data_bin blob,region_id bigint,location_id bigint,location_type_id int,device_id bigint,lane_id int,direction_id int,speed int,license_plate text,lp_type int,image_url1 text,image_url2 text,image_url3 text,has_image int,last_captured int,PRIMARY KEY (info_id));
                        ''' % dss,timeout=60)
                    
            session.execute('''
                CREATE TABLE IF NOT EXISTS d_%s (info_id uuid,capture_time timestamp,data_bin blob,region_id bigint,location_id bigint,location_type_id int,device_id bigint,lane_id int,direction_id int,speed int,license_plate text,lp_type int,image_url1 text,image_url2 text,image_url3 text,has_image int,last_captured int,PRIMARY KEY (info_id))
                ''' % ds,timeout=60)
            end = timeit.default_timer()
            print 'use time',end-start
        session.execute('''
            CREATE TABLE IF NOT EXISTS last_captured (license_plate text, capture_time int, PRIMARY KEY (license_plate));
            ''',timeout=60)
# has_image
# location_type_id
    except OperationTimedOut,e:
        end = timeit.default_timer()
        print 'Operation TimedOut',str(e),end-start
    except WriteTimeout,e:
        end = timeit.default_timer()
        print 'Write Timeout',str(e),end-start
    except:
        fp = StringIO.StringIO()
        traceback.print_exc(file=fp)
        print 'create table error',fp.getvalue()
        fp.close
    else:
        print 'done'
        
def drop_table():
    start = timeit.default_timer()
    try:
        config_file = open(os.path.dirname(os.path.abspath(__file__)) + '/config.yaml')
        config = yaml.safe_load(config_file)
        config_file.close()    
        cluster = Cluster(contact_points = config['scylla']['hosts'], port = 9042,max_schema_agreement_wait = 300)
        print 'connect scylla...'
        session = cluster.connect()
        KEYSPACE = "yisa_oe"
        print 'set_keyspace yisa_oe'
        session.set_keyspace(KEYSPACE)
        today = datetime.datetime.now()
        for d in xrange(-500,-365):#删除一年前的正式表
            day = today + datetime.timedelta(days = d)
            ds = day.strftime('%Y%m%d')
            print 'drop table [d_%s]'%ds,
            start = timeit.default_timer()
            session.execute("DROP TABLE IF EXISTS d_%s;" % ds,timeout=300)
            end = timeit.default_timer()
            print 'use time',end-start
        for d in xrange(-365,-1):#删除3天前的临时表
            day = today + datetime.timedelta(days = d)
            ds = day.strftime('%Y%m%d')
            dss = day.strftime('%Y%m%d%H')
            print 'drop table [today_%s]'%ds,
            start = timeit.default_timer()
            session.execute("DROP TABLE IF EXISTS today_%s;" % ds,timeout=300)
            for h in xrange(0,24):
                dayh = day + datetime.timedelta(hours = h)
                dss = dayh.strftime('%Y%m%d%H')
                print 'drop table hours',dss,            
                session.execute("DROP TABLE IF EXISTS today_%s;" % dss,timeout=300)
            end = timeit.default_timer()
            print 'use time',end-start
        
    except OperationTimedOut,e:
        end = timeit.default_timer()
        print 'Operation TimedOut',str(e),end-start
    except WriteTimeout,e:
        end = timeit.default_timer()
        print 'Write Timeout',str(e),end-start
    except:
        fp = StringIO.StringIO()
        traceback.print_exc(file=fp)
        print 'drop table error',fp.getvalue()
        fp.close
    else:
        print 'done'
        
def alert_table():
    start = timeit.default_timer()
    try:
        config_file = open(os.path.dirname(os.path.abspath(__file__)) + '/config.yaml')
        config = yaml.safe_load(config_file)
        config_file.close()    
        cluster = Cluster(contact_points = config['scylla']['hosts'], port = 9042,max_schema_agreement_wait = 30)
        print 'connect scylla...'
        session = cluster.connect()
        KEYSPACE = "yisa_oe"
        session.set_keyspace(KEYSPACE)
        today = datetime.datetime.now()
        for d in xrange(-300,100):
            day = today + datetime.timedelta(days = d)
            ds = day.strftime('%Y%m%d')
            print 'alert table',ds,
            start = timeit.default_timer()
            try:
                if d>=0:
                    session.execute('''
                        ALTER TABLE today_%s ADD lp_type int;
                        ''' % ds,timeout=30)
                session.execute('''
                    ALTER TABLE d_%s ADD lp_type int;
                    ''' % ds,timeout=30)
            except Exception,e:
                print str(e)
            end = timeit.default_timer()
            print 'use time',end-start
    except OperationTimedOut,e:
        end = timeit.default_timer()
        print 'Operation TimedOut',str(e),end-start
    except WriteTimeout,e:
        end = timeit.default_timer()
        print 'Write Timeout',str(e),end-start
    except:
        fp = StringIO.StringIO()
        traceback.print_exc(file=fp)
        print 'create table error',fp.getvalue()
        fp.close
    else:
        print 'done'
        
if __name__ == "__main__":
    p = OptionParser(usage="python %prog -m arg [1:create tables 2:drop tables]")  
    p.add_option("-m", "--method", action="store", type="int", help='select a method',default=0)
    options, args = p.parse_args()
    method = options.method
    if method==1:
        print 'select create tables'
        create_table()
    elif method==2:
        print 'select drop tables'
        drop_table()
    elif method==3:
        print 'select alert tables'
        alert_table()
    else:
        print 'params error'